Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO

/*Drop stored procedures if they exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'cambioEstado2a3' and type = 'P')

BEGIN
    DROP PROCEDURE cambioEstado2a3
END

GO


/*Done droping stored procedures*/


CREATE PROCEDURE cambioEstado2a3		(	  @idProyectoRemanentes INT ,
											  @rowsToInsert VARCHAR (4000) ,  
											  @idProyecto INT , 
											  @outParam INT OUTPUT						

										)
AS
	
	DECLARE @row AS VARCHAR (1000);
	DECLARE @startPos AS INT;
	DECLARE @endPos AS INT;
	DECLARE @sp INT;
	DECLARE @ep INT;	 
	DECLARE @char varchar (10);

	DECLARE @idM AS INT;
	DECLARE @idP AS INT;
	DECLARE @idB AS INT;
	DECLARE @cAs AS FLOAT;
	DECLARE @cRe AS FLOAT;
	DECLARE @cResta AS FLOAT;
	
	DECLARE @rows AS INT;

	DECLARE @tempId INT;
	
	SET @startPos = 1;
	SET @endPos = 1;
	SET @row = 'init';	
	SET @char = 'x';	

	SET @sP = 1;
	SET @ep = 1;	

	SET @tempId = 1 ;

	SET @idM = -1;
	SET @idP = -1;
	SET @idB = -1;
	SET @cAs = -1;
	SET @cRe = -1;
	SET @cResta = -1;
	
	BEGIN TRANSACTION;
	
		BEGIN TRY
			
			
			CREATE TABLE #tempSplit 
				( 
				  id INT PRIMARY KEY NOT NULL , 
				  idTipoMat 		INT , 
				  idBodega 			INT ,
				  idProy			INT ,
				  cAsign			FLOAT
				 )
			
		
				 
			WHILE (@endPos != 0 AND @endPos < LEN (@rowsToInsert)  )

			BEGIN

				INSERT INTO #tempSplit VALUES (@tempId , 0 , 0 , 0 , 0 );	

				SET @endPos = (SELECT CHARINDEX (';' , @rowsToInsert , @startPos  ) ) ;
				SET @row = ( SELECT SUBSTRING (@rowsToInsert , @startPos  , ABS (@endPos - @startPos ) ) );
			
					SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
					SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );	

					update #tempSplit set idTipoMat = CAST (@char AS INT)
					WHERE id = @tempId;

					SET @sp = @ep + 1;
			
					SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
					SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );
	
					update #tempSplit set idBodega = CAST (@char AS INT)
					WHERE id = @tempId;				

					SET @sp = @ep + 1;

					SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
					SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );
	
					update #tempSplit set idProy = CAST (@char AS INT)
					WHERE id = @tempId;				

					SET @sp = @ep + 1;
					
					SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
					SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );
	
					update #tempSplit set cAsign = CAST (@char AS FLOAT)
					WHERE id = @tempId;				

					SET @sp = @ep + 1;

				SET @startPos =  @endPos + 1;
				SET @tempId = @tempId + 1;
		
			END;	 
			
			DECLARE cursor_ins CURSOR FOR 
			SELECT MATERIAL_REAL.IDTIPOMATERIAL, MATERIAL_REAL.IDPROYECTO,
			MATERIAL_REAL.IDBODEGA, MATERIAL_REAL.CANTIDADREAL
			FROM MATERIAL_REAL
			WHERE MATERIAL_REAL.IDPROYECTO = @idProyecto;

			OPEN cursor_ins;

			FETCH NEXT FROM cursor_ins INTO @idM , @idP , @idB , @cRe;
		 
			WHILE @@FETCH_STATUS = 0

			BEGIN
				
					SELECT @cAs = cAsign 
					FROM #tempSplit
					WHERE idTipoMat = @idM AND idBodega = @idB AND idProy = @idP;
					
					SET @cResta = @cRe - @cAs;
					
					UPDATE MATERIAL_REAL SET MATERIAL_REAL.CANTIDADREAL = MATERIAL_REAL.CANTIDADREAL + @cResta
					WHERE MATERIAL_REAL.IDPROYECTO = @idProyectoRemanentes
					AND MATERIAL_REAL.IDBODEGA = @idB
					AND MATERIAL_REAL.IDTIPOMATERIAL = @idM;
					
					
					/*El material esta completamente identificado, asi que si no hubo un update,
					Es porque ya no esta en la base de datos, por ende, hay que insertarlo denuevo*/
				 
					IF (@@ROWCOUNT = 0 )
						BEGIN
						
								INSERT INTO MATERIAL_REAL VALUES (@idM, @idB, @idProyectoRemanentes, @cResta, 0);
					
						END
					 
					FETCH NEXT FROM cursor_ins INTO @idM , @idP , @idB , @cRe;
			END;

			CLOSE cursor_ins;
			DEALLOCATE cursor_ins;
			
			DELETE FROM MATERIAL_REAL WHERE MATERIAL_REAL.IDPROYECTO = @idProyecto;
			
			DROP TABLE #tempSplit;
			
			DELETE FROM MATERIAL_REAL 
			WHERE MATERIAL_REAL.CANTIDADREAL <= 0;
			
			UPDATE PROYECTO SET PROYECTO.IDESTADOPROYECTO = 3 WHERE PROYECTO.IDPROYECTO = @idProyecto;
			
			SET @outParam = 1;
			
			COMMIT TRANSACTION; 
		
		END TRY
		
		BEGIN CATCH
			
			SET @outParam = -1;
			ROLLBACK TRANSACTION;
		
		END CATCH;
	
GO			

